(1) Field of the Invention
This invention relates to a method of preventing unnecessary joins between tables in a database from being executed and, in particular, to a method for automatically rewriting SQL statements to achieve this.
(2) Description of Related Art
The concept of a join between tables in a database is well known. A simple example will be described here with reference to FIG. 1. This shows two tables with the names EMP and DEPT. The first table EMP lists the names of the employees of a company under the column ENAME and the number of the department for which they work under the column DEPTNO. The table DEPT has a similar column named DEPTNO in which each department number is only listed once and adjacent to this is a column entitled DNAME giving the names of the respective departments. It can be seen that a many to one relationship exists between the tables EMP and DEPT via their respective DEPTNO columns. That is to say that each value appears only once under the DEPTNO column of DEPT but can appear many times under the corresponding column of EMP. In this context, table EMP is referred to as the detail table and table DEPT is referred to as the master table.
In the table DEPT, the column DEPTNO has values that are unique in each row and this column is referred to as the primary key of the table. This primary key is typically indexed allowing fast access to each row. In table EMP, the column DEPTNO is referred to as the foreign key. This is not necessarily an indexed column although typically it is.
If it were desired to extract the names of the employees and their respective department names, then typically, a view would be defined as follows: SELECT ENAME, DNAME FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
This view may be known as EMPDEPT. Then, an SQL statement may refer to this view EMPDEPT such as:
SELECT ENAME, DNAME FROM EMPDEPT
This statement requires the join to be executed in order to extract the required data from both tables. However, there are instances in which it is not necessary to execute the join in order to extract the required data but nevertheless, the database still executes the join. Clearly, it is desirable to provide a method in which this unnecessary execution of joins can be prevented.